package com.bifrost.repository;

import com.bifrost.entity.Coupon;
import com.bifrost.entity.Customer;
import com.bifrost.entity.MerchantStore;
import com.bifrost.entity.Order;
import com.bifrost.entity.OrderItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author rangf
 * @date 2017/11/14
 */
public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {
    /**
     * 计算每个订单下item的数量
     *
     * @param orderId orderId
     * @return Long
     */
    @Query(value = "SELECT SUM(I.SKU_QUANTITY) FROM ORDER_ITEM I WHERE I.ORDER_ID=?1", nativeQuery = true)
    Long countOrderItem(Long orderId);

    /**
     * 根据订单号查询会员订单
     *
     * @param customer    customer
     * @param orderNumber 订单号
     * @return
     */
    Order findByCustomerAndOrderNumber(Customer customer, String orderNumber);

    @Query(value = "SELECT DESCRIPTION FROM ORDER_MAIN where ORDER_NUMBER=?1", nativeQuery = true)
    String findDescByNum(String orderNum);

    /**
     * 获取订单
     *
     * @param id       id
     * @param customer customer
     * @return
     */
    Order findByOrderIdAndCustomer(Long id, Customer customer);

    /**
     * 获取用户订单
     *
     * @param customer customer
     * @return
     */
    List<Order> findAllByCustomer(Customer customer);

    /**
     * 获取一门店下该会员所有订单
     *
     * @param customer
     * @param store
     * @return
     */
    List<Order> findAllByCustomerAndMerchantStore(Customer customer, MerchantStore store);

    /**
     * 修改订单状态
     *
     * @param orderId
     * @param status
     * @return 订单
     */
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query(value = "UPDATE ORDER_MAIN O SET O.ORDER_STATUS=?2 ,O.SHIPPMENT_STATUS=?3 WHERE O.ORDER_ID=?1 " ,nativeQuery = true)
    int updateStatus(Long orderId, String status,String shippmentStatus);

    /**
     * 根据外部订单号查找订单
     *
     * @param number
     * @return
     */
    Order findFirstByOutTradeNo(String number);

    /**
     * 查询所有未支付订单
     *
     * @return
     */
    @Query(value = "SELECT * FROM ORDER_MAIN  WHERE ORDER_STATUS='PURCHASED' AND ORDER_TYPE <> 'REFUND' ORDER BY DATE_PURCHASED", nativeQuery = true)
    List<Order> findAllUnpaidOrders();

    /**
     * 取消订单
     *
     * @param orderId
     * @return
     */
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query(value = "UPDATE ORDER_MAIN  SET ORDER_STATUS='CANCELED' WHERE ORDER_ID=?1 AND ORDER_STATUS ='PURCHASED' ", nativeQuery = true)
    int cancelOrder(Long orderId);

    /**
     * 查询所有有效的退单
     *
     * @param orderNumber
     * @return
     */
    @Query(value = "SELECT * FROM ORDER_MAIN  WHERE  RELATED_ORDER_NUMBER =?1 AND ORDER_TYPE = 'REFUND' AND ORDER_STATUS <> 'CANCELED' ", nativeQuery = true)
    List<Order> findVaildRefundOrders(String orderNumber);

    /**
     * 查询在退货中的订单数量
     *
     * @param customerId
     * @return
     */
    @Query(value = "SELECT count(1) FROM ORDER_MAIN  WHERE  CUSTOMER_ID =?1 AND ORDER_TYPE = 'REFUND' AND ORDER_STATUS NOT IN ('CANCELED','FINISHED' )", nativeQuery = true)
    long countRefundingOrders(Long customerId);

    /**
     * 获取待评价订单数量
     *
     * @param customerId
     * @param merchantStore
     * @return
     */
    @Query(value = "SELECT COUNT(*) FROM ORDER_MAIN O WHERE O.CUSTOMER_ID=?1 " +
            "AND O.MERCHANT_ID=?2 AND O.COMMENT_STATUS=?3 AND O.ORDER_STATUS='FINISHED'", nativeQuery = true)
    long countNotCommentOrders(Long customerId, Long merchantStore, String commentStatus);

    List<Order> findAllByOrderStatus(String status);

    /**
     * 获取所有未评论订单
     *
     * @param commentStatus
     * @param orderStatus
     * @return
     */
    List<Order> findAllByCommentStatusAndOrderStatus(String commentStatus, String orderStatus);

    /**
     * 自动确认订单
     * @param orderId
     * @return
     */
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query(value = "UPDATE ORDER_MAIN  SET ORDER_STATUS='CONFIRMED',SHIPPMENT_STATUS='PACKAGE' WHERE ORDER_ID=?1 AND ORDER_STATUS ='PURCHASED' ",nativeQuery = true)
    int confirmOrder(Long  orderId);

    /**
     * 注意!!!该方法返回的实体只包含orderId、orderTotal属性
     * @param orderId
     * @return
     */
    @Query("select new com.bifrost.entity.Order(o.orderId,o.orderTotal) from Order o where o.orderId = ?1")
    Order findByOrderId(Long orderId);

    List<Order> findAllByCouponId(Long couponId);

    Order findFirstByOrderNumber(String transNo);

    /**
     * 打印取货单
     *
     * @param orderId orderId
     * @return
     */
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query(value = "UPDATE ORDER_MAIN SET PRINT_COUNT=PRINT_COUNT+1 WHERE ORDER_ID=?1", nativeQuery = true)
    int updatePrintCount(Long orderId);

    /**
     * 自动确认订单
     * @param orderId
     * @return
     */
    @Modifying(clearAutomatically = true)
    @Transactional
    @Query(value = "UPDATE ORDER_MAIN  SET ORDER_STATUS='FINISHED' WHERE ORDER_ID=?1 AND ORDER_STATUS ='PURCHASED' ",nativeQuery = true)
    int finishOrder(Long  orderId);

    Integer countByCustomerAndCouponIdAndOrderStatus(Customer customer, Long couponId, String purchased);
}
